<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
	
	
class M_level_fees Extends MY_Model
{
	private $__table = 'level_fees';
	private $_ftable = 'fees';
	private $_ltable = 'levels';
	private $__seftable = 'student_enrollment_fees';
	private $ci;
	
	public function __construct()
	{
		parent::__construct();
		$this->ci =& get_instance();
	}

	public function get_gp()
	{
		/*
		* gp_id,gp_code,gp_desc,gp_stat
		*/
		$sql = "SELECT gp_id,gp_code,gp_desc,gp_stat FROM grading_periods g WHERE g.is_set = 'yes' limit 1";
		$query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	public function check_level_fee_isset()
	{
		
	}
	
	public function get_grade_level($id=FALSE)
	{
		if($id == FALSE){
			$query = $this->db->select(array('level_id','level_desc','level_code'))->get($this->_ltable);
		}else{
			$query = $this->db->select(array('level_id','level_desc'))->where('level_id',$id)->get($this->_ltable);
		}
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_fees()
	{
		$query = $this->db->select(array('fee_id','fee_name','fee_desc','fee_rate'))
						  ->where('is_other_fee',1)
						  ->where('fee_stat','active')
						  ->get($this->_ftable);
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_sel_fees($id)
	{
		$sql = "SELECT f.fee_id,f.fee_name,f.fee_desc,f.fee_rate
				FROM level_fees lvl_fee
				LEFT JOIN fees f ON f.fee_id = lvl_fee.lf_fee_id
				LEFT JOIN levels l ON l.level_id = lvl_fee.lf_level_id
				WHERE l.level_id = ?
				AND lvl_fee.lf_level_id = ?
				AND f.fee_stat = ?
				AND f.is_other_fee = 1";
		$query = $this->db->query($sql,array($id,$id,'active'));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_tuition_fees_set_for_current_level($id)
	{
	
		$sql = "SELECT fees.fee_id,
					   fees.fee_name,
					   fees.fee_desc,
					   fees.fee_rate
				FROM fees
				WHERE fees.grade_level = ? 
				AND fee_stat = 'active' 
				AND is_tuition_fee = 1;
				";	
		$query = $this->db->query($sql,array($id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_misc_fees($e_id)
	{
		$grade_level =  $this->db->select('e_grade_level')
								 ->where('e_id',$e_id)
								 ->get('enrollments');
		$id = $grade_level->row()->e_grade_level;	 
		
		
		
		if( $id == NULL)
		{
			return FALSE;
		}else
		{
			$sql = "SELECT fees.fee_id,
						   fees.fee_name,
						   fees.fee_desc,
						   fees.fee_rate
					FROM fees
					WHERE fee_stat = 'active' 
					AND is_misc_fee = 1
					AND grade_level = ?
					";	
			$query = $this->db->query($sql,array($id));
			
			$str = $this->db->last_query();
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		}
	}
	
	public function update_grade_level_fees($data,$id)
	{
		if($this->db->where('lf_level_id',$id)->delete($this->__table))
		{
			if($this->db->insert_batch($this->__table, $data))
			{
				return array('status'=>'true');
			}else{
				return array('status'=>'false');
			}
		}else{
			return array('status'=>'nodelete');
		}
	}
	
	public function get_mrt_fees_for_level($id,$mode=TRUE)
	{
		$reg_tuit = $this->get_new_tuit_fees($id);
		$reg_acc = $this->get_new_acc_fees($id);
		$reg_misc = $this->get_new_misc_fees($id);
		$sql = "SELECT level_fees.lf_id as fee_id,
					   f.fee_name,
					   f.fee_rate,
						is_misc_fee,
						is_tuition_fee,
						is_cca_fee					   
				from level_fees 
				left join fees f on level_fees.lf_fee_id = f.fee_id
				WHERE f.fee_new = 'yes' AND f.fee_old != 'yes' AND level_fees.lf_level_id = ?
				";
		$misc_fees = $this->db->query($sql,array($id));
		$res_misc_fees =$misc_fees->num_rows() >=1 ? $misc_fees->result_array() : array(NULL);

		$data = array_merge($reg_tuit,$reg_acc,$reg_misc,$res_misc_fees);
		
		$total = 0;
		
		foreach($data as $k => $v)
		{
			if(!empty($v))
			{
				$view[$k]['value'] = '<ul style="list-style-type:none; padding:0px; margin:0px;"><li style="float:left; width:60%; text-align:top;">'.$v['fee_name'].'</li><li style="float:left; text-align:right; width:40%;">  &#8369; '.money($v['fee_rate']).'</li></ul><span style="clear:both;"></span>';
				$total += $v['fee_rate'];
				if(isset($v['is_misc_fee']) OR isset($v['is_tuition_fee']))
				{
					if($v['is_misc_fee']){
						$view[$k]['type'] ='Miscellaneous Fee';
					}elseif($v['is_tuition_fee']){
						$view[$k]['type'] ='Tuition Fee';
					}
				}else{
					$view[$k]['type'] ='Others';
				}
			}else{
				$view['value'] = NULL;
				$view['type'] = NULL;
			}
		}
		$k++;
		
		$view[$k]['value'] = '<ul style="list-style-type:none; padding:0px; margin:0px;"><li style="float:left; width:60%; text-align:top; font-weight:bold;">Total</li><li style="float:left; text-align:right; width:40%; font-weight:bold;"> &#8369; '.money($total).'</li></ul><span style="clear:both;"></span>';
		
		$temp = '<ul>';
		foreach($view as $key => $val)
		{
			$temp .= '<li><span class="value">'.$val['value'].'</span></li>';
		}
		$temp .= '</ul>';
		if($mode == TRUE){
			return $temp;
		}else{
			return $data;
		}
	}
	
	public function get_old_mrt_fees_for_level($id,$mode=TRUE)
	{
		//$reg_tuit = $this->get_tuit_and_misc_fees($id);
		$reg_tuit = $this->get_old_tuit_fees($id);
		$reg_acc = $this->get_old_acc_fees($id);
		$reg_misc = $this->get_old_misc_fees($id);
		$sql = "SELECT lf_id as fee_id,
					   fee_name,
					   fee_rate,
					   is_misc_fee,
						is_tuition_fee,
						is_cca_fee
				from level_fees
				left join fees f on lf_fee_id = f.fee_id
				WHERE fee_new != 'yes' AND fee_old = 'yes' AND lf_level_id = ?
				";
		$misc_fees = $this->db->query($sql,array($id));
		$res_misc_fees =$misc_fees->num_rows() >=1 ? $misc_fees->result_array() : array(NULL);
		
	
		$data = array_merge($reg_tuit,$reg_acc,$reg_misc,$res_misc_fees);
		
		$total = 0;
		
		foreach($data as $k => $v)
		{
			if(!empty($v))
			{
				$view[$k]['value'] = '<ul style="list-style-type:none; padding:0px; margin:0px;"><li style="float:left; width:60%; text-align:top;">'.$v['fee_name'].'</li><li style="float:left; text-align:right; width:40%;"> &#8369; '.money($v['fee_rate']).'</li></ul><span style="clear:both;"></span>';
				$total += $v['fee_rate'];
				if(isset($v['is_misc_fee']) OR isset($v['is_tuition_fee']))
				{
					if($v['is_misc_fee']){
						$view[$k]['type'] ='Miscellaneous Fee';
					}elseif($v['is_tuition_fee']){
						$view[$k]['type'] ='Tuition Fee';
					}
				}else{
					$view[$k]['type'] ='Others';
				}
			}else{
				$view['value'] = NULL;
				$view['type'] = NULL;
			}
		}
		$k++;
		
		$view[$k]['value'] = '<ul style="list-style-type:none; padding:0px; margin:0px;"><li style="float:left; width:60%; text-align:top; font-weight:bold;">Total</li><li style="float:left; text-align:right; width:40%; font-weight:bold;"> &#8369; '.money($total).'</li></ul><span style="clear:both;"></span>';
		
		$temp = '<ul>';
		foreach($view as $key => $val)
		{
			$temp .= '<li><span class="value">'.$val['value'].'</span></li>';
		}
		$temp .= '</ul>';
		if($mode == TRUE){
			return $temp;
		}else{
			return $data;
		}
	}
	
	public function get_tuit_and_misc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND (is_misc_fee =1
				OR is_tuition_fee = 1)
				AND fee_stat = "active"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_new_tuit_and_misc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND (is_misc_fee =1
				OR is_tuition_fee = 1 
				OR is_cca_fee = 1)
				AND fee_stat = "active"
				AND fee_new = "yes"
				AND fee_old != "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_new_tuit_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_tuition_fee = 1 
				AND fee_stat = "active"
				AND fee_new = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_new_misc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_misc_fee =1
				AND fee_stat = "active"
				AND fee_new = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_new_acc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_cca_fee = 1
				AND fee_stat = "active"
				AND fee_new = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_old_tuit_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_tuition_fee = 1 
				AND fee_stat = "active"
				AND fee_old = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_old_misc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_misc_fee =1
				AND fee_stat = "active"
				AND fee_old = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}
	
	public function get_old_acc_fees($id)
	{
		$sql = 'SELECT fee_id,
				fee_name,
				fee_rate,
				is_misc_fee,
				is_tuition_fee,
				is_cca_fee
				FROM fees
				WHERE grade_level = ?
				AND is_cca_fee = 1
				AND fee_stat = "active"
				AND fee_old = "yes"
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result_array() : array(NULL);
	}

	public function generate_student_fees($id)
	{
		$this->ci->load->model(array('M_enrollees'));
		$g= $this->M_enrollees->get_enrollee_prof_2($id);
		
		if($g)
		{	
			return $this->_get_fees_set_for_current_level($g->level_id,$g->e_ay_id,$g->e_enrollee_type,$id);
		}else{
			$message = (object)array('log'=>'Unable to retrieve student information.','stat'=>'false');	
			return $message;
		}
	}
	
	private function _get_fees_set_for_current_level($level_id,$sy,$typer,$enroll_id)
	{
		$tuition_set = FALSE;
		$matri_set = FALSE;
		$misc_set = FALSE;
		$gperiod = $this->get_gp();

		if($this->set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod->gp_id,$sy,$typer) == TRUE)
		{
			$tuition_set = TRUE;
		}

		if($this->_insert_matriculation_fee($enroll_id,$sy,$level_id,$typer) == TRUE)
		{
			$matri_set = TRUE;
		}

		if($this->_insert_miscellaneous_fee($enroll_id,$sy,$level_id,$typer) == TRUE)
		{
			$misc_set = TRUE;
		}
		
		if($tuition_set OR $matri_set OR $misc_set){
			$message = (object)array('log'=>'Successfully generated student fees.','stat'=>'true');
		}else{
			$message = (object)array('log'=>'Unable to generate and insert fees to student account.','stat'=>'false');
		}
		
		return $message;
	}

		/*
		function that inserts tuition to enrollee
	*/
	public function set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		/*
			Selects all tuition fee that is active.
		*/
		/* old function
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_tuition_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}
		*/
		$sql = "SELECT f.fee_id,f.fee_name,f.fee_rate,f.ay_id
				FROM fees f 
				WHERE f.is_tuition_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}		
		$query = $this->db->query($sql,array($level_id));
		if($query->num_rows() >= 1 )
		{
			$res = $query->result();
			$new_val = array();
			foreach($res as $key => $re):
				if($this->c_sy->id != $re->ay_id)
				{
					$sq = $this->db->where('fee_id',$re->fee_id)
					               ->where('ay_id',$this->c_sy->id)
					               ->get('fee_for_sy');					   
					if($sq->num_rows() >=1)
					{
						$v = $sq->row();
						$re->fee_rate = $v->fee_rate;
						array_push($new_val,$re);
					}else{
						array_push($new_val,$re);
					}
				}else{
					array_push($new_val,$re);
				}
			endforeach;

			$data = $new_val;
		}else{
			$data = FALSE;
		} 
		
		//format tuition fees for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_tuition_fee'] = 1;
				$count++;
			}
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted tuition fee
				call method insert_miscellaneous_fee()
				this method will insert misc fee for the student
			*/
			if($this->db->affected_rows() >= 1 )
			{
				return TRUE;
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}

	/*
		Inserts matriculation fees to students, called after inserting the miscellaneous fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	private function _insert_matriculation_fee($id,$sy,$level_id,$e_stat)
	{
		/* old function
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_cca_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}
		*/
		$sql = "SELECT f.fee_name,f.fee_rate,f.ay_id
				FROM fees f 
				WHERE f.is_cca_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}
		$query = $this->db->query($sql,array($level_id));
		if($query->num_rows() >= 1 )
		{
			$res = $query->result();
			$new_val = array();
			foreach($res as $key => $re):
				if($this->c_sy->id != $re->ay_id)
				{
					$sq = $this->db->where('fee_id',$re->fee_id)
					               ->where('ay_id',$this->c_sy->id)
					               ->get('fee_for_sy');					   
					if($sq->num_rows() >=1)
					{
						$v = $sq->row();
						$re->fee_rate = $v->fee_rate;
						array_push($new_val,$re);
					}else{
						array_push($new_val,$re);
					}
				}else{
					array_push($new_val,$re);
				}
			endforeach;
		
			$data = $new_val;
		}else{
			$data = FALSE;
		} 
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_cca_fee'] = 1;
				$count++;
			}
			
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			
			if($this->db->affected_rows() >= 1)
			{
				return TRUE;
			}else{
				return FALSE;
			}

		}else{
			return FALSE;
		}
	}

	/*
		Inserts misc fees to students, called after inserting the tuition fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	private function _insert_miscellaneous_fee($enroll_id,$sy,$level_id,$e_stat)
	{
		/* old function
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_misc_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
		*/
		$sql = "SELECT f.fee_name,f.fee_rate,f.ay_id
				FROM fees f 
				WHERE f.is_misc_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}
		$query = $this->db->query($sql,array($level_id));
		if($query->num_rows() >= 1 )
		{
			$res = $query->result();
			$new_val = array();
			foreach($res as $key => $re):
				if($this->c_sy->id != $re->ay_id)
				{
					$sq = $this->db->where('fee_id',$re->fee_id)
					               ->where('ay_id',$this->c_sy->id)
					               ->get('fee_for_sy');					   
					if($sq->num_rows() >=1)
					{
						$v = $sq->row();
						$re->fee_rate = $v->fee_rate;
						array_push($new_val,$re);
					}else{
						array_push($new_val,$re);
					}
				}else{
					array_push($new_val,$re);
				}
			endforeach;
		
			$data = $new_val;
		}else{
			$data = FALSE;
		} 
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_misc_fee'] = 1;
				$count++;
			}
			
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			
			if($this->db->affected_rows() >= 1)
			{
				return TRUE;
			}else{
				return FALSE;
			}

		}else{
			return FALSE;
		}
	}
}